package Hangman;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.ejb.EJBException;
import javax.ejb.EntityContext;
import javax.ejb.RemoveException;

/**
 *
 * @author beomjoongkwon
 */

public class MySQLAccessBean implements javax.ejb.EntityBean {
        private Connection connect = null;
        private Statement statement = null;
        private PreparedStatement preparedStatement = null;
        private ResultSet resultSet = null;
        private String dbAddress = "jdbc:mysql://localhost:3306/HangmanUsers?";
        private String userIDPwd = "user=root&password=";
        private String basicTable = "Users";
        private String detailTable = "Administrator";

        // Check specific id is in the DB or not
        public boolean isMember(String name) throws Exception {
            try {
                String result = "";

                // This will load the MySQL driver, each DB has its own driver
                Class.forName("com.mysql.jdbc.Driver");
                // Setup the connection with the DB
                connect = DriverManager.getConnection(dbAddress + userIDPwd);

                // Statements allow to issue SQL queries to the database
                statement = connect.createStatement();
                // Result set get the result of the SQL query
                resultSet = statement
                                .executeQuery("select Name from " + basicTable);

                // Check DB has same name on it
                while (resultSet.next()) {
                    String id = resultSet.getString("Name");

                    if (name.equals(id)) {
                        // If there is same id, return true
                        return true;
                    }
                }
                // If no same name, return false
                return false;
            } catch (Exception e) {
                throw e;
            } finally {
                close();
            }
        }

        public boolean remove(String username) {
            try {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager.getConnection(dbAddress + userIDPwd);

            // Statements allow to issue SQL queries to the database
            statement = connect.createStatement();
                // Result set get the result of the SQL query
            statement.executeUpdate("delete from " + basicTable + " where Name='" + username + "'");
            return true;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close();
                return false;
            }
        }

        // Register New User to both DB table
        public boolean registerNewUser(String name, String password) throws Exception {
            try {
                // This will load the MySQL driver, each DB has its own driver
                Class.forName("com.mysql.jdbc.Driver");
                // Setup the connection with the DB
                connect = DriverManager.getConnection(dbAddress + userIDPwd);

                // Statements allow to issue SQL queries to the database
                statement = connect.createStatement();

                // Insert New register to BasicUserInfo DB
                statement.executeUpdate("INSERT INTO Users(`Name`, `Password`) VALUES ('" + name + "', '" + password + "')");
                System.out.println("UPDATED");
                // Insert New register to DetailUserInfo DB
                //statement.executeUpdate("insert into " + detailTable +
                                       // " values ('" + name + "', 0, 0)");

                return true;

            } catch (Exception e) {
                e.printStackTrace();
                return false;
            } finally {
                close();
            }
        }

        public String isValidPassword(String name) throws Exception {
            try {
                String result = "";

                // This will load the MySQL driver, each DB has its own driver
                Class.forName("com.mysql.jdbc.Driver");
                // Setup the connection with the DB
                connect = DriverManager.getConnection(dbAddress + userIDPwd);

                // Statements allow to issue SQL queries to the database
                statement = connect.createStatement();
                // Result set get the result of the SQL query
                resultSet = statement
                                .executeQuery("select * from " + basicTable);

                // Check DB has same name on it
                while (resultSet.next()) {
                    String id = resultSet.getString("Name");
                    String pwd = resultSet.getString("Password");

                    if (name.equals(id)) {
                        // If there is same id, return true
                        return pwd;
                    }
                }
                // If no same name, return false
                return null;
            } catch (Exception e) {
                throw e;
            } finally {
                close();
            }
        }

        public void increseWin(String name) throws SQLException {
            try {

                Class.forName("com.mysql.jdbc.Driver");

                connect = DriverManager.getConnection(dbAddress + userIDPwd);
                statement = connect.createStatement();
                resultSet = statement.executeQuery("select * from " + detailTable);

                // Check DB has same name on it
                while (resultSet.next()) {
                    String id = resultSet.getString("id");
                    int winCount = resultSet.getInt("win");
                    winCount++;

                    if (name.equals(id)) {
                        // If there is same id, return true
                        statement.executeUpdate("Update " + detailTable + " SET " +
                                                "win= " + winCount + " WHERE " +
                                                "id='" + id + "'");
                        break;
                    }
                }

            } catch (Exception e) {

            } finally {
                close();
            }
        }

        public void increseLose(String name) throws SQLException {
            try {

                Class.forName("com.mysql.jdbc.Driver");

                connect = DriverManager.getConnection(dbAddress + userIDPwd);
                statement = connect.createStatement();
                resultSet = statement.executeQuery("select * from " + detailTable);

                // Check DB has same name on it
                while (resultSet.next()) {
                    String id = resultSet.getString("id");
                    int loseCount = resultSet.getInt("lose");
                    loseCount++;

                    if (name.equals(id)) {
                        // If there is same id, return true
                        statement.executeUpdate("Update " + detailTable + " SET " +
                                                "lose= " + loseCount + " WHERE " +
                                                "id='" + id + "'");
                        break;
                    }
                }

            } catch (Exception e) {

            } finally {
                close();
            }
        }

        public void resetWinLose(String name) throws SQLException {
            try {

                Class.forName("com.mysql.jdbc.Driver");

                connect = DriverManager.getConnection(dbAddress + userIDPwd);
                statement = connect.createStatement();
                resultSet = statement.executeQuery("select * from " + detailTable);

                // Check DB has same name on it
                while (resultSet.next()) {
                    String id = resultSet.getString("id");

                    if (name.equals(id)) {
                        // If there is same id, return true
                        statement.executeUpdate("Update " + detailTable + " SET " +
                                                "win=0, lose=0  WHERE " +
                                                "id='" + id + "'");
                        break;
                    }
                }

            } catch (Exception e) {

            } finally {
                close();
            }
        }

        public void changePassword(String name, String newPwd) throws SQLException {
            try {

                Class.forName("com.mysql.jdbc.Driver");

                connect = DriverManager.getConnection(dbAddress + userIDPwd);
                statement = connect.createStatement();
                resultSet = statement.executeQuery("select * from " + basicTable);

                // Check DB has same name on it
                while (resultSet.next()) {
                    String id = resultSet.getString("id");

                    if (name.equals(id)) {
                        // change password to new password


                        break;
                    }
                }

            } catch (Exception e) {

            } finally {
                close();
            }
        }

        private void writeMetaData(ResultSet resultSet) throws SQLException {
                //      Now get some metadata from the database
                // Result set get the result of the SQL query

                System.out.println("The columns in the table are: ");

                System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
                for  (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
                        System.out.println("Column " +i  + " "+ resultSet.getMetaData().getColumnName(i));
                }
        }

        private String writeResultSet(ResultSet resultSet) throws SQLException {
                String result = "";
                // ResultSet is initially before the first data set
                while (resultSet.next()) {
                        // It is possible to get the columns via name
                        // also possible to get the columns via the column number
                        // which starts at 1
                        // e.g. resultSet.getSTring(2);
                        String id = resultSet.getString("id");
                        String pwd = resultSet.getString("pwd");

                        result = result + id + pwd;

                        //System.out.println("id: " + id);
                        //System.out.println("pwd: " + pwd);

                }
                return result;
        }

        // You need to close the resultSet
        private void close() {
                try {
                        if (resultSet != null) {
                                resultSet.close();
                        }

                        if (statement != null) {
                                statement.close();
                        }

                        if (connect != null) {
                                connect.close();
                        }
                } catch (Exception e) {

                }
        }

    @Override
    public void setEntityContext(EntityContext ctx) throws EJBException, RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void unsetEntityContext() throws EJBException, RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void ejbRemove() throws RemoveException, EJBException, RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void ejbActivate() throws EJBException, RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void ejbPassivate() throws EJBException, RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void ejbLoad() throws EJBException, RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void ejbStore() throws EJBException, RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

}